package www.lagou.app;

import entity.Phone;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.junit.Test;
import utils.DruidUtils;
import www.lagou.entity.Account;

import java.sql.SQLException;

public class testConnection {
    /**
     * 按照步骤 实现卡号：1122334455向55443332211转账5000元的操作；
     */
    @Test
    public void testAvaliable() throws SQLException {
        //1.使用连接池创建QueryRunner对象；--> 自动模式
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        //2.判断转出方是否有足够余额，如果不足，提示信息：”余额不足！”，并结束程序；
        //2.1 占位符方式sql
        String sql = "SELECT * FROM account WHERE card = ?";

        //2.2 执行sql,并使用编写好的JAVABEAN
        Account account = qr.query(sql, new BeanHandler<Account>(Account.class), "1122334455");
        if (account.getBalance() < 5000) {
            System.out.println("余额不足！");
            return;
        } else {
            System.out.println("用户:" + account.getUsername() + " " + "余额为：" + account.getBalance());
        }
    }

    @Test
    public void testTransfer() throws SQLException {
        QueryRunner qr2 = new QueryRunner(DruidUtils.getDataSource());

        //3.通过卡号 进行转账的操作
        String sql2 = "UPDATE account SET balance = balance - ? WHERE card = ?";
        Object[] param = {5000.0, "1122334455"};
        qr2.update(sql2, param);

        String sql3 = "UPDATE account SET balance = balance + ? WHERE card = ?";
        Object[] param2 = {5000.0, "55443332211"};
        qr2.update(sql3, param2);

    }

    @Test
    public void testAccord() throws SQLException {
        //4.转账结束后, 将转入、转出记录分别写入到Transaction表中。
        QueryRunner qr3 = new QueryRunner(DruidUtils.getDataSource());
        String sql4 = "insert into TRANSACTION VALUES(?,?,?,?,?) ";
        Object[] param3 = {null,"1122334455", "转出", 5000, "2021-06-30"};
        qr3.update(sql4,param3);

        String sql5 = "insert into TRANSACTION VALUES(?,?,?,?,?) ";
        Object[] param4 = {null,"55443332211", "转入", 5000, "2021-06-30"};
        qr3.update(sql5,param4);
    }

    /*
        需求1:  查询价格高于2000元，生产日期是2019年之前的所有手机
     */
    @Test
    public void testFindPhone() throws SQLException {
        QueryRunner qr4 = new QueryRunner(DruidUtils.getDataSource());
        String sql5 = "SELECT pname FROM phone WHERE price > ? AND (prodate > ?)";
        Object[] param = {2000, "2018-12-31"};
        Phone p = qr4.query(sql5, new BeanHandler<Phone>(Phone.class),param);
        System.out.println("获取到的手机名称是：" + p.getPname());

    }

    /**
     *  需求2:  查询所有颜色是白色的手机信息
     */
    @Test
    public void testFindColor() throws SQLException {
        QueryRunner qr5 = new QueryRunner(DruidUtils.getDataSource());
        String sql6 = "SELECT * FROM phone WHERE color = ?";
        Phone p2 = qr5.query(sql6,new BeanHandler<Phone>(Phone.class),"白色");
        System.out.println("白色手机的信息是：" + p2);


    }





}

